package com.zhp.dao;

import com.zhp.common.Constant;
import com.zhp.dao.base.BaseDao;
import com.zhp.model.Article;
import com.zhp.model.User;
import com.zhp.utils.JDBCUtil;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ArticleDao extends BaseDao<Article> {
    public ArticleDao() {
        super(Article.class);
    }

    public List<Map<String, Object>> findArticles(String uId, int currentPage, String name) {
        String sql = "select * ,(SELECT COUNT(*) FROM favorite where a_id = article.id) as count,(SELECT COUNT(*) FROM favorite where a_id = article.id and u_id=?) as 'iffavorite' from article ORDER BY publish_date DESC LIMIT ?,?";
        if (name != null) {
            sql = "select * ,(SELECT COUNT(*) FROM favorite where a_id = article.id) as count,(SELECT COUNT(*) FROM favorite where a_id = article.id and u_id=?) as 'iffavorite' from article where title like ? ORDER BY publish_date DESC LIMIT ?,? ";
        }
        List<Map<String, Object>> list = new ArrayList<>();
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            if (null == name) {
                list = queryRunner.query(sql, new MapListHandler(), uId, (currentPage - 1) * Constant.PAGE_SIZE, Constant.PAGE_SIZE);
            } else {
                list = queryRunner.query(sql, new MapListHandler(), uId, "%" + name + "%", (currentPage - 1) * Constant.PAGE_SIZE, Constant.PAGE_SIZE);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<Map<String, Object>> findMyArticles(String uId, int currentPage, String name) {
        String sql = "select * ,(SELECT COUNT(*) FROM favorite where a_id = article.id) as count,(SELECT COUNT(*) FROM favorite where a_id = article.id and u_id=?) as 'iffavorite' from article where EXISTS (SELECT 1 FROM favorite where favorite.a_id = article.id and favorite.u_id=?) ORDER BY publish_date DESC LIMIT ?,?";
        if (name != null) {
            sql = "select * ,(SELECT COUNT(*) FROM favorite where a_id = article.id) as count,(SELECT COUNT(*) FROM favorite where a_id = article.id and u_id=?) as 'iffavorite' from article where title like ? and EXISTS (SELECT 1 FROM favorite where favorite.a_id = article.id and favorite.u_id=?) ORDER BY publish_date DESC LIMIT ?,? ";
        }
        List<Map<String, Object>> list = new ArrayList<>();
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            if (null == name) {
                list = queryRunner.query(sql, new MapListHandler(), uId, uId, (currentPage - 1) * Constant.PAGE_SIZE, Constant.PAGE_SIZE);
            } else {
                list = queryRunner.query(sql, new MapListHandler(), uId, "%" + name + "%", uId, (currentPage - 1) * Constant.PAGE_SIZE, Constant.PAGE_SIZE);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public long getArticleTotal(String name) {
        long total = 0L;
        String sql = "select count(*) as total from article";
        if (null != name) {
            sql = "select count(*) as total from article where title like ?";
        }
        Map<String, Object> map = new HashMap<>();
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            if (null == name) {
                map = queryRunner.query(sql, new MapHandler());
            } else {
                map = queryRunner.query(sql, new MapHandler(), "%" + name + "%");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        if (map != null) {
            total = Long.parseLong(map.get("total").toString());
        }
        return total;

    }

    public long getMyArticleTotal(String uid, String name) {
        long total = 0L;
        String sql = "select count(*) as total from article where (SELECT COUNT(*) FROM favorite where favorite.a_id = article.id and favorite.u_id=?)" ;
        if (null != name) {
            sql = "select count(*) as total from article where title like ? and (SELECT COUNT(*) FROM favorite where favorite.a_id = article.id and favorite.u_id=?) ";
        }
        Map<String, Object> map = new HashMap<>();
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            if (null == name) {
                map = queryRunner.query(sql, new MapHandler(),uid);
            } else {
                map = queryRunner.query(sql, new MapHandler(), "%" + name + "%",uid);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        if (map != null) {
            total = Long.parseLong(map.get("total").toString());
        }
        return total;

    }

    public Number getFavoriteCount(String id) {

        String sql = "SELECT COUNT(*) FROM favorite where a_id=?";
        List<Map<String, Object>> list = new ArrayList<>();
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            return queryRunner.query(sql, new ScalarHandler<>(), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public List<User> getUserFavoriteByIds(String id) {
        String sql = "select u.* \n" +
                "from user u\n" +
                "where u.id in (SELECT f.u_id FROM favorite f where f.a_id=?) and EXISTS(select 1 from user_focus uf where uf.user_id = ? and uf.user_focus_id =u.id)";
        List<User> list = new ArrayList<>();
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            BasicRowProcessor processor = new BasicRowProcessor(new GenerousBeanProcessor());
            list = queryRunner.query(sql, new BeanListHandler<>(User.class, processor), id,id);
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
